SQL Queries - cont.

Select Statements - Queries

-- Select all columns from all rows 
SELECT * FROM employees;

-- Select specific columns 
SELECT first_name, last_nam FROM employees;

Specifying query conditions allows retrieving precisely the information you need from the database.

• The WHERE clause is used to filter records based on specified conditions.
• The NOT operator is used to negate a condition in the WHERE clause, meaning it retrieves rows where the specified condition is false.

-- select first and last name of employees with id 1 or id 2
SELECT first_name, last_name
FROM employees
WHERE employee_id = 1 OR employee_id = 2;

SELECT first_name, last_name FROM employees
WHERE last_name = 'Doe';

SELECT first_name, last_name 
FROM employees
WHERE NOT department = 'Sales';

1. Operators

a. Mathematical, Logical Operators

SELECT * FROM employees
WHERE employee_id <= 3;

SELECT * FROM employees
WHERE employee_id > 3;

--- both != and <>  means "not equal to"
SELECT * FROM employees
WHERE employee_id != 1;

SELECT * FROM employees
WHERE employee_id <> 1;

b. String Concatenation Operator ||

SELECT first_name, last_name, 
first_name || ' ' || last_name AS name 
FROM your_table_name;

BETWEEN… AND

The BETWEEN operator is used in WHERE clauses to filter the result set based on a range of values.

SELECT * FROM employees
WHERE birth_date BETWEEN '2001-01-01' AND '2010-12-31';

2. ORDER BY

The ORDER BY clause is used to sort the result set of a query by one or more columns. By default, it sorts the results in ascending order, but you can specify ASC for ascending or DESC for descending order.

-- sort by alphabetical order of the first_name column
SELECT * FROM employees
ORDER BY first_name; 

-- first sorted by first_name, and within each first_name, they are further sorted by birth_date.
SELECT * FROM employees
ORDER BY first_name, birth_date; 

3. LIMIT

The LIMIT clause in SQL is used to specify the maximum number of rows that should be returned by a query. It is often used to restrict the result set to a manageable size, particularly when dealing with large datasets.

SELECT * FROM employees
ORDER BY first_name DESC
LIMIT 2;

OFFSET

OFFSET is often used in conjunction with the LIMIT (or FETCH) clause to control which rows are returned in a query result set.

-- retrieve rows starting from the 11th row with a limit of 10 rows:
SELECT employee_id, first_name, birth_date
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS;

4. IN

The IN keyword in SQL is used to specify multiple possible values for a column in a WHERE clause.

-- Select employees with IDs 1, 2, and 3
SELECT * FROM employees
WHERE employee_id IN (1, 2, 3);

5. AS - Alias

The AS keyword in SQL is used for aliasing, allowing you to give a table or a column a temporary name, making your queries more readable by referring to a column by a different name in the results.

SELECT first_name AS forename FROM employees; 

• Aliases are primarily created in the SELECT clause and can be used in subsequent parts of the query at higher levels (e.g., in the ORDER BY clause or in an outer query).

• Aliases cannot be used in the WHERE clause of the same query level where they are defined, because the WHERE clause is evaluated before the SELECT clause.
• Similar to WHERE, aliases typically cannot be used in the GROUP BY or HAVING clauses at the same query level where they are defined. Instead, the original expression must be used.

6. DISTINCT

The DISTINCT keyword in SQL is used to remove duplicate rows from a result set. It ensures that unique values are returned in a query.

SELECT DISTINCT sex FROM employees; 
-- returns M and F

7. LIKE - Wildcards

Wildcards are special characters used in WHERE clauses with the LIKE operator to search for specific patterns in a column. They allow for more flexible string matching.

1. Percent (%): Represents zero, one, or multiple characters.

-- Find all employees whose last name starts with 'S'
SELECT * FROM employees WHERE last_name LIKE 'S%';

-- Find all employees whose last name ends with 'son'
SELECT * FROM employees WHERE last_name LIKE '%son';

-- Find all employees with the word 'smith' anywhere in the last name
SELECT * FROM employees WHERE last_name LIKE '%smith%';

2. Underscore (_): Represents a single character.

-- Find all product codes with exactly three characters and end with “23”.
SELECT * FROM products WHERE code LIKE '_23';

-- Find all product codes starting with A followed by 2 digts
SELECT * FROM products WHERE code LIKE 'A__';

8. CASE and ELSE

The CASE is a conditional expression tht allows you to perform if-then-else logic within a SQL query. It evaluates a list of conditions and returns one of multiple possible result expressions.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE else_result]
END

Example 1: Using CASE in SELECT clause.

-- Assume we have a facilities table with the following data:

-- name                | monthlymaintenance
-- Tennis Court 1      | 200
-- Tennis Court 2      | 200
-- Badminton Court     | 50
-- Table Tennis        | 30
-- Massage Room 1      | 3000
-- Massage Room 2      | 3000
-- Squash Court        | 80
-- Snooker Table       | 15
-- Pool Table          | 15

SELECT 
    name, 
    CASE 
        WHEN monthlymaintenance > 100 THEN 'expensive'
        WHEN monthlymaintenance <= 100 THEN 'cheap'
    END AS cost
FROM facilities;

-- or can be written as
SELECT 
    name, 
    CASE 
        WHEN monthlymaintenance > 100 THEN 'expensive'
        ELSE 'cheap'
    END AS cost
FROM facilities;

-- Result:
-- name                | cost
-- Tennis Court 1      | expensive
-- Tennis Court 2      | expensive
-- Badminton Court     | cheap
-- Table Tennis        | cheap
-- Massage Room 1      | expensive
-- Massage Room 2      | expensive
-- Squash Court        | cheap
-- Snooker Table       | cheap
-- Pool Table          | cheap

**Example 2: ** Using CASE in WHERE clause. This example is only used to demonstrate how CASE can be used in a WHERE statement and isn’t the best way to write the query for this scenario. The query can be simplified to produce the same result without using CASE.

-- Sample data for orders table:
-- order_id | customer_name | order_date  | amount
-- 1        | Alice         | 2023-01-15  | 100.00
-- 2        | Bob           | 2023-02-20  | 150.00
-- 3        | Charlie       | 2023-04-10  | 200.00
-- 4        | David         | 2023-07-05  | 120.00
-- 5        | Eve           | 2023-11-30  | 180.00

SELECT *
FROM orders
WHERE 
    CASE 
        WHEN MONTH(order_date) BETWEEN 1 AND 3 THEN 'Q1'
        WHEN MONTH(order_date) BETWEEN 4 AND 6 THEN 'Q2'
        WHEN MONTH(order_date) BETWEEN 7 AND 9 THEN 'Q3'
        ELSE 'Q4'
    END = 'Q1';

-- Simplified query 
SELECT * 
FROM orders 
WHERE MONTH(order_date) BETWEEN 1 AND 3;

-- Result:
-- order_id | customer_name | order_date  | amount
-- 1        | Alice         | 2023-01-15  | 100.00
-- 2        | Bob           | 2023-02-20  | 150.00

**Example 3: ** Using CASE in ORDER BY clause.

-- Sample data for employees table:
-- employee_id | name  | department | salary
-- 1           | John  | Sales      | 50000.00
-- 2           | Jane  | Marketing  | 55000.00
-- 3           | Mike  | IT         | 60000.00
-- 4           | Sarah | Sales      | 52000.00
-- 5           | Tom   | HR         | 48000.00

SELECT *
FROM employees
ORDER BY
    CASE
        WHEN department = 'Sales' THEN 1
        WHEN department = 'Marketing' THEN 2
        ELSE 3
    END;

-- Result:
-- employee_id | name  | department | salary
-- 1           | John  | Sales      | 50000.00
-- 4           | Sarah | Sales      | 52000.00
-- 2           | Jane  | Marketing  | 55000.00
-- 3           | Mike  | IT         | 60000.00
-- 5           | Tom   | HR         | 48000.00

Subquery - Nested Queries

-- find names of all employees who have sold over 30,000 to a single client
SELECT employees.first_name, employees.last_name
FROM employee
WHERE employee.emp_id IN (
	SELECT emp_id
	FROM works_with 
	WHERE works_with.total_sales > 3000; 
)

1. Main Query

SELECT employees.first_name, employees.last_name
FROM employees
WHERE employee.emp_id IN (
    ...
);

• Purpose: This part of the query selects the first_name and last_name of employees from the employees table.
• Condition: It filters the employees to include only those whose emp_id is found in the result of the nested query.

2. Nested Query - Subquery

SELECT emp_id
FROM works_with 
WHERE works_with.total_sales > 3000;

• Purpose: This nested query (or subquery) selects the emp_id from the works_with table.
• Condition: It filters to include only those rows where total_sales is greater than 3000.

All subqueries in the FROM clause must have an alias. SQL requires any result set (such as a table or subquery) referenced in the FROM clause to have a name, even if it's a temporary name provided by an alias.

SELECT 
    subquery.product_name, 
    subquery.total_sales
FROM 
    (SELECT 
        p.name AS product_name, 
        SUM(o.quantity) AS total_sales
     FROM 
        products p
     JOIN 
        orders o ON p.product_id = o.product_id
     GROUP BY 
        p.name
    ) AS subquery;